Locations Search Part I

Christopher Marks

2019-01-18

PART II: Update Data and Analyze

This is the second part of a two-part vignette designed to collect and analyze Twitter posts from Boston and Guatemala by searching for location hashtags. This part of the vignette carries out the database initialization and initial status collections. This part of the vignette consists of two sub-parts: (a) updating the twitter database with new posts, and (b) analysis. These two sub-parts can be run together or independently, as often as required.

Initialization

We begin by loading the twinfoR namespace, establishing the Twitter database connection to the sqlite database created in Part I of this vignette, and loading the authentication vector. See (and execute) the location_search_1 vignette if you haven’t already done so.

This code assumes the auth.vector from Part I is saved. If it isn’t, reauthenticate using the authorize_IT() function as done in Part I.

library(twinfoR)
setwd("~/twitter_locations")
con <- twitter_database("locations.sqlite")
load("auth_vector.RData") 

Update Database with New Statuses

This function should take considerably less time to run than the initial call, because it only collects new statuses.

update_search(
  con
)

# Summarize database
summarize_database(con)
#> No query user table.
#> Search queries: 4
#> Search categories:
#> location
#> since_id
#> Users: 11454
#> Statuses: 21860
#> Unique user mentions: 5633
#> Unique hashtags: 16021
#> Unique URLs: 7303
#> Total Images: 6385
#> Unique Images: 5472
#> Total relationships: 0
#> Database file size: 88MB

Analysis

The summary output from the last section shows the columns of the query_text table. Let’s take a closer look at this table, which we created in Part I of this vignette.

df <- DBI::dbReadTable(con,"query_text")
knitr::kable(
  df,
  caption = "The `query_text` table"
)
The query_text table
id query_text location since_id
1 #Boston Boston 1086382976902942721
2 #GuatemalaCity Guatemala City 1086352390666756098
3 #CiudaddeGuatemala Guatemala City 1086135555807690752
4 #CiudadGuatemala Guatemala City 1085775331158577152

Any column in this table can be used as where.criteria in the analysis functions. We are going to use the location column.

Top hashtags from Boston and Guatemala City in the last week

Top Hashtags Used by Boston and Guatemala City
Boston.Hashtag Count
#Boston 13668
#Atlanta 890
#Houston 842
#Chicago 759
#Miami 657
#Autographed 642
#NYC 612
#CNN 529
#denver 469
#California 468
GuatemalaCity.Hashtag Count
#guatemalacity 46
#Guatemala 35
#CiudadDeGuatemala 33
#guate 13
#cdgt 10
#muniguate 9
#centralamerica 8
#guatephoto 7
#zona1 7
#Guategram 6

Top usermentions from Boston and Guatemala City in the last week

Boston User Mentions
Screen_Name Name Mentions
@SportsDen2016 East Coast Sports 523
@redostoneage Truth Tweeter 329
@marty_walsh Mayor Marty Walsh 321
@FerRaverta Fernanda Raverta 244
@wizard_leather TheWizardOfLeather 185
@Patriots New England Patriots 172
@surjboston SURJ Boston 126
@KassandraSeven Kassandra Seven 126
@CityOfBoston City of Boston 120
@malunahe María Navarro 105
Guatemala City User Mentions
Screen_Name Name Mentions
@CRGuatemalteca CruzRojaGT 5
@CityMaxMix CityMax Mix 3
@EmixtraPablo Pablo Morales 2
@spadegraphics Spade Graphics by Dave OfTheJungle 2
@ElLuchin1 TEMBLOR ⚡️ 2
@info502 Guatemala 1
@goldmindslatino goldmindslatino 1
@Spotify Spotify 1
@republicagt República 1
@mary_sor Sor Mary Lopez 1

Top URLs posted by Boston and Guatemala City in the last week

Top Boston and Guatemala City URLs
Boston.URL Count
http://www.thewizardofleather.com 306
http://natsjobs.com/find-a-job/?search_location=Boston 99
http://thewizardofleather.com 71
http://bit.ly/YAC1Ic 53
http://www.bostonsegwaytours.net 38
http://www.bostonsegwaytoursinc.com/gift 37
http://line.me/S/sticker/1270761 34
http://twib.in/l/LRyMbLaL5xXR 31
http://smarturl.it/Rahzarspitarhyme/applemusic 28
https://bit.ly/2GYcdUQ 24
GuatemalaCity.URL Count
https://buff.ly/2VPQh1v 2
https://www.instagram.com/p/BslblXBA7N0/?utm_source=ig_twitter_share&igshid=1cm5mpwyb9ngp 2
http://bit.ly/2FHVF0T 1
http://www.citymax-mix.com 1
https://buff.ly/2sBl9p6 1
https://newsinamerica.com/pdcc/aries-el-lider-presenta-encima-de-mi/ 1
https://open.spotify.com/track/2zfm5s9Db7ppg9kt3G1gH1 1
https://twitter.com/ABC7Chicago/status/1083921649488015360 1
https://twitter.com/muniguate/status/1085571579994689541 1
https://twitter.com/muniguate/status/1085578070604558337 1

Top media from Boston and Guatemala City in the last week

Top Media Posted by Boston and Guatemala City
Boston.image_url count
http://pbs.twimg.com/media/Dw0l87FX0Aw9ui_.jpg 39
http://pbs.twimg.com/media/Dw0l87IX0AMJ3YR.jpg 39
http://pbs.twimg.com/media/DwqcmABUcAIySiw.jpg 38
http://pbs.twimg.com/media/Dpp_1YGWkAAhA6-.jpg 36
http://pbs.twimg.com/amplify_video_thumb/1083439532240973824/img/JXjcTq1tcj96dGPC.jpg 34
http://pbs.twimg.com/media/CgIqd39UMAA3Lva.jpg 16
http://pbs.twimg.com/media/CgK6_7kUAAAcckG.jpg 16
http://pbs.twimg.com/media/Dw4rfI1WsAEDeuF.jpg 13
http://pbs.twimg.com/media/Dm7OrVFXcAELAXk.jpg 11
http://pbs.twimg.com/media/Dw5xxAJWoAI0xjf.jpg 11
GuatemalaCity.image_url count
http://pbs.twimg.com/media/DwjddPEWoAERPkK.jpg 3
http://pbs.twimg.com/media/DwefXouWkAU5kBS.png 1
http://pbs.twimg.com/media/DwefZdiWoAEkA23.png 1
http://pbs.twimg.com/media/DwefbNGWoAMGIub.jpg 1
http://pbs.twimg.com/media/DwleQsFV4AAA7Vr.jpg 1
http://pbs.twimg.com/media/DwleRKWU8AIJ6px.jpg 1
http://pbs.twimg.com/media/DwupUUfUUAA7Ita.jpg 1
http://pbs.twimg.com/media/DwupV4dV4AAphk3.jpg 1
http://pbs.twimg.com/media/DwvEqXdW0AAzb1Q.jpg 1
http://pbs.twimg.com/media/DwvEyZ6WoAAdQre.jpg 1
Boston Image Guatemala City Image
Top Boston Image Top Guatemala City Image

The top_media and status_media have many options for saving and rendering images. See documentation for details.

Top tweeters Boston and Guatemala City in the last week

Top Boston Tweeters
Name Location Description Tweets
Truth Tweeter Corrupt City of Chicago I love America. I love our Constitution. I hope we return to our LIBERTARIAN values! #MAGA 466
María Navarro Houston, TX Team 🧙🏻‍♂️followizard❤️ #f4f #FollowMe #followback #Follow4follow #Followher #USA🇺🇸🇺🇸 #Houston #Texas 184
Roxanne Shelaider Malibu, CA 🇺🇸🇺🇸🇺🇸🇺🇸🇺🇸 I´m a #Malibu fashion lover team #followizard #Followme #follow4follow, #f4f #Followback #follower #USA 🇺🇸🇺🇸🇺🇸🇺🇸🇺🇸 169
Nathalie Watson Ottawa, Ontario America’s 1st Job Alert System 98
TheRealBostonSegway Boston, MA Don’t be fooled, there’s only one! Right in the heart of it all at 199 State St. and we are The best way to see the city! 617-421-1234 https://t.co/RTeeSA3dL8 90
Boston Plow Internet A new #Boston community with less noise & junk on @plowio. Follow for an invite to join our open beta! 89
#Londonislovinit London Promoting everything that is #LONDON under the Hashtag #Londonislovinit Use this 4 a RT everytime #Londonhour every Thurs 8-9pm 👊 89
Boston Jobs Boston, MA Find Jobs and Work in Boston, Massachusetts at https://t.co/OZ8R0vTPpw! – – #BOSTON #MA #WORK #JOBS #CAREER #EMPLOY #CELTICS #REDSOX #BRUINS #CANNONS #BLADES 71
East Coast Sports FT Lauderdale, FLA Welcome to East Coast Sports! We Specialize in RARE Autographed New England Sports Memorabilia, and RARE items signed by Legends of the game both Past & Present 69
Timothy Breitzmann Chicago Follow my link, check out my art @ Society6. We need to put down our hate and reach for Enlightenment or this Human experiment will not end well for any of us. 68
Top Guatemala City Tweeters
Name Location Description Tweets
IO al Desnudo ™🇬🇹 Guatemala Comunicólogo, #AmoGuate #BuenaVibra 10
Raúl Contreras Guatemala En la vida nada es caSUalidad, todo es caUSalidad. ¡Así es la vida! 5
Jfranco87 Guatemala Café 87 5
Guatemala 4
Zo United States GOD . FAMILY . TRAVELER . RISK TAKER . AAMU ALUM . GLOBAL MARKETING 4
CityMax Mix Guatemala #1 en Bienes Raices 4
вяαd αѕтυяιαѕ t(’’t• Guatemala Fisicoculturista Guatemalteco 🇬🇹 ™🏃20 años de edad •Entrenador Personal 💯🏋🏽Instagram- bradasturias 👻Snap- brad_asturias #Björk 🖤 3
CITYMAX Guatemala Guatemala Franquicia Bienes Raices Ciudad Guatemala, Apartamentos, Casas, Locales Comerciales, Edificios, Terrenos, Oficinas y Bodegas Renta/Venta ☎️PBX(502)2493-5555 3
Spade Graphics by Dave OfTheJungle Guatemala Proud member of Zoey Love Art and Spade Graphics 3
Nancy Yarelis Cortez Surque Guatemala 3

Most liked tweets Boston and Guatemala City in the last week

Most Liked Boston Tweets
Name Created Likes Text
Joey McIntyre 2019-01-14 04:05:01 955 Great day for #Boston- @patriots win big and so does Kira Mac 😊 – 1st All-Around at Boston Invitational. – #proudpapa #kiwakiwa 😭 https://t.co/wyQn3FKKue
Sarah Shahi 2019-01-14 23:18:39 703 You’ll want to 🤛🏼 her and 💋 her at the same time. Meet Rachel. Get ready. She’s dynamite in a tiny package @BenAffleck @AldisHodge @jonathanmtucker @Showtime #Boston #wickedsmaht https://t.co/TKaDV9PQM6
BostonTweet 2019-01-11 22:19:13 424 Brrrr #Boston https://t.co/WtYCPP6cIA
Fernanda Raverta 2019-01-16 17:41:18 351 ¿Qué es la pedagogía de la crueldad? – – Un operativo en plena madrugada con más de 200 efectivos para desalojar a trabajadores que fueron víctimas del vaciamiento de una empresa. – – Mi mayor repudio a este accionar de la Justicia y mi solidaridad con los trabajadores de la #Boston https://t.co/FgzdBs5iuI
David Wade 2019-01-16 00:12:01 329 Of all the stores that have closed in #Boston through the years, Filene’s Basement is the one I miss most. And it’s not even close. How about you? https://t.co/19wfYHaN7b
Red 2019-01-13 21:36:18 312 Daily reminder that Gods walk among us. #Boston #Sports #Blessed https://t.co/oHEtAbc1S5
Ayanna Pressley 2019-01-15 23:10:23 296 Point of clarification: Just so there is no confusion or trouble, especially on today. I am not a member of #AKA I am however a proud sister of @linksinc #Boston chapter. Many of my link sisters & new #CBC colleagues (see Lauren below) are Happy #FoundersDay #HU #1908 #sisterhood https://t.co/lpmMtXLLpY
Luis Bremer 2019-01-16 14:19:24 175 Donde había una fuente de trabajo y las medialunas más ricas de Mardel….. #Tristeza #Boston #MardelPlata https://t.co/4UAOZgjCRk
Ahmed/DaBaby Fan Account 2019-01-15 22:19:39 163 i think Carmelo Anthony would be perfect for the @celtics , they need the veteran leadership that Kyrie is looking for right now in their recent struggles. – – #CelticNation #Boston https://t.co/HNUi91CB3e
Kassandra Seven 2019-01-13 14:09:00 153 This food bank is serving #Boston-area #USCoastGuard members who are not being paid due to the #shutdown. – – Please spread the word and consider donating. – – Massachusetts Military Support Foundation https://t.co/Xklih0IxFQ
Most Liked Guatemala City Tweets
Name Created Likes Text
José E. Valdizán 2019-01-14 14:38:08 6 Edificio del Banco Industrial y Plazuela 11 de marzo #guatemala #guatemalacity #streets #guate #streetstyle #streetview #view #streetlife #city #citylife #centralamerica #dji… https://t.co/GmIbX4nR9b
CruzRojaGT 2019-01-12 17:53:25 6 #CiudadDeGuatemala – – Accidente de tránsito se suscita en 8av 2Calle zona 1, dos vehículos colisionan, socorristas de #CruzRojaGT le brindaron atención prehospitalaria a una persona con heridas leves en el lugar, no ameritó ser trasladada. https://t.co/rCUw93iwvT
TEMBLOR ⚡️ 2019-01-12 19:53:24 4 🇬🇹➡#Temblor 4.3⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/5hF1R0T03x
José E. Valdizán 2019-01-18 07:05:49 3 Vista Hermosa zona 15, Guatemala #cityscape #city #guatemala #guatemalacity #guate #guategram #view #drone #droneview #dronestagram #vistahermosaguatemala #life #citylife #citylifestyle… https://t.co/IX3ImlKSel
CityMax Mix 2019-01-16 20:55:37 3 Apartamento con Linea Blanca en Parque 11 – – Renta Q5,000.00 mantenimiento incluido, dos habitaciones, 2 baños. – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/voPzn9Xm3Q
TEMBLOR ⚡️ 2019-01-12 19:52:50 3 🇬🇹➡#Temblor 4.3 ⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/ze8zg8k183
CityMax Mix 2019-01-15 21:18:26 2 ¡Bodega en San Cristobal! – - Terreno de 9 por 20 – - 325m² totales de construcción – - Mezzanine de 56m² – - 1.5 baños – - Energía eléctrica bifasica – - Instalación de agua potable municipal – - 8 espacios para estacionamiento – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX https://t.co/Vekq3mCnop
CityMax Mix 2019-01-14 20:41:57 2 ¡Casa cerca de Sankris Mall! – – De dos niveles, con 3 habitaciones con closets, 2 baños completos, espacio para 2 vehículos. – – Venta Q700,000.00 – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/9jg1i00xXR
вяαd αѕтυяιαѕ t(’’t• 2019-01-11 02:54:14 2 ⭕️⭕️ – – #BradAsturias #TeamArango #LOCO #PersonalTrainer #ChaosGuatemala #Diet – #Ripped #alphanutrition #Guatemalacity #generationiron #fitnessgym #bodybuilding #training #best… https://t.co/dH6DRnCIHm
goldmindslatino 2019-01-17 15:11:36 2 #conferencia #presencial de #forex este 26 de Enero en #ciudaddeguatemala. #aprende Con nosotros sin Redes de #mercadeo ni pagos mensuales. Pares de enfoque: #xauusd #gbpjpy. #estrategias de otro #nivel totalmente en #español

Most retweeted tweets Boston and Guatemala City in the last week

Most Retweeted Boston Tweets
Name Created Retweet_Count Text
Fernanda Raverta 2019-01-16 17:41:18 268 ¿Qué es la pedagogía de la crueldad? – – Un operativo en plena madrugada con más de 200 efectivos para desalojar a trabajadores que fueron víctimas del vaciamiento de una empresa. – – Mi mayor repudio a este accionar de la Justicia y mi solidaridad con los trabajadores de la #Boston https://t.co/FgzdBs5iuI
MoreCoin 2019-01-11 17:30:19 196 Which $MORE party are you going to? #Hollywood #OC #Boston #Miami #Chicago? 18 places to use crypto. #usecase #adoption #iwantmore #bittrex #blockchain #cryptotequila https://t.co/7YuE4EvPlW
Kassandra Seven 2019-01-13 14:09:00 155 This food bank is serving #Boston-area #USCoastGuard members who are not being paid due to the #shutdown. – – Please spread the word and consider donating. – – Massachusetts Military Support Foundation https://t.co/Xklih0IxFQ
Sarah Shahi 2019-01-14 23:18:39 79 You’ll want to 🤛🏼 her and 💋 her at the same time. Meet Rachel. Get ready. She’s dynamite in a tiny package @BenAffleck @AldisHodge @jonathanmtucker @Showtime #Boston #wickedsmaht https://t.co/TKaDV9PQM6
Luis Bremer 2019-01-16 14:19:24 75 Donde había una fuente de trabajo y las medialunas más ricas de Mardel….. #Tristeza #Boston #MardelPlata https://t.co/4UAOZgjCRk
Joey McIntyre 2019-01-14 04:05:01 65 Great day for #Boston- @patriots win big and so does Kira Mac 😊 – 1st All-Around at Boston Invitational. – #proudpapa #kiwakiwa 😭 https://t.co/wyQn3FKKue
It’s Going Down 2019-01-18 11:06:24 63 Saturday in #Boston, a variety of anti-racist organizations are mobilizing against a group of neo-Nazis with Resist Marxism - who are planning to crash the #WomensMarch. A small group of #ProudBoys also claim they will wear disguises to attend in #Orlando. https://t.co/DvzFhHDz47 https://t.co/bwF6XT7VrL
Carbon Black, Inc. 2019-01-14 21:27:23 63 Honored to be named the best place to work in #Boston this year by @BuiltinBOS. We remain committed to investing in the growth of our employees, and are excited for the opportunities that #2019 has in store for us - https://t.co/CCCHBQToWe
Today In History 2019-01-17 05:14:00 59 Today in 1706: American #statesman Benjamin #Franklin is born in #Boston. He is the youngest boy in a family of 17 children. Among his many accomplishments, he helped draft the Declaration of #Independence. He died on April 17, 1790. #history #AmericanHistory https://t.co/hcVqQB5xOl
Ahmed/DaBaby Fan Account 2019-01-15 22:19:39 51 i think Carmelo Anthony would be perfect for the @celtics , they need the veteran leadership that Kyrie is looking for right now in their recent struggles. – – #CelticNation #Boston https://t.co/HNUi91CB3e
Most Retweeted Guatemala City Tweets
Name Created Retweet_Count Text
CruzRojaGT 2019-01-12 17:53:25 4 #CiudadDeGuatemala – – Accidente de tránsito se suscita en 8av 2Calle zona 1, dos vehículos colisionan, socorristas de #CruzRojaGT le brindaron atención prehospitalaria a una persona con heridas leves en el lugar, no ameritó ser trasladada. https://t.co/rCUw93iwvT
WorldShare 2019-01-18 13:00:34 1 The thousands of people living around the rubbish dump in #GuatemalaCity are exposed to dangerous living conditions daily. Our partner Potter’s House work to provide protection and safety as well as developing self-worth and hope. https://t.co/XTzDySKj0R https://t.co/5lavH76xuM
José E. Valdizán 2019-01-18 07:05:49 1 Vista Hermosa zona 15, Guatemala #cityscape #city #guatemala #guatemalacity #guate #guategram #view #drone #droneview #dronestagram #vistahermosaguatemala #life #citylife #citylifestyle… https://t.co/IX3ImlKSel
CityMax Mix 2019-01-16 20:55:37 1 Apartamento con Linea Blanca en Parque 11 – – Renta Q5,000.00 mantenimiento incluido, dos habitaciones, 2 baños. – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/voPzn9Xm3Q
CityMax Mix 2019-01-15 21:18:26 1 ¡Bodega en San Cristobal! – - Terreno de 9 por 20 – - 325m² totales de construcción – - Mezzanine de 56m² – - 1.5 baños – - Energía eléctrica bifasica – - Instalación de agua potable municipal – - 8 espacios para estacionamiento – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX https://t.co/Vekq3mCnop
CityMax Mix 2019-01-14 20:41:57 1 ¡Casa cerca de Sankris Mall! – – De dos niveles, con 3 habitaciones con closets, 2 baños completos, espacio para 2 vehículos. – – Venta Q700,000.00 – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/9jg1i00xXR
goldmindslatino 2019-01-17 15:11:36 1 #conferencia #presencial de #forex este 26 de Enero en #ciudaddeguatemala. #aprende Con nosotros sin Redes de #mercadeo ni pagos mensuales. Pares de enfoque: #xauusd #gbpjpy. #estrategias de otro #nivel totalmente en #español
Guatemala 2019-01-16 16:50:19 1 OTRA OPCIÓN AL #TRÁNSITOGT – – De ingreso a #CiudadDeGuatemala por el sur del país. Tome nota de otra vía al #TráficoGT. https://t.co/zpTq2SRywb
TEMBLOR ⚡️ 2019-01-12 19:53:24 1 🇬🇹➡#Temblor 4.3⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/5hF1R0T03x
TEMBLOR ⚡️ 2019-01-12 19:52:50 1 🇬🇹➡#Temblor 4.3 ⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/ze8zg8k183

Don’t forget to disconnect!